package com.system.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.system.entity.CollectInfo;
import com.system.util.CloseResourceUtil;
import com.system.util.GetConnectionUtils;

public class CollectDaoImpl implements ICollectDao {

	public String addOrganizeInfo(CollectInfo collectInfo) throws SQLException {
		Connection connection = GetConnectionUtils.getoneConnection();
		String sql = "INSERT INTO collect_info VALUES(?,?,?,?,?,?,?,?,?)";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, collectInfo.getCollectNum());
		preparedStatement.setString(2, collectInfo.getFoodNum());
		preparedStatement.setString(3, collectInfo.getFoodName());
		preparedStatement.setInt(4, collectInfo.getCollectTotal());
		preparedStatement.setString(5, collectInfo.getCollect1());
		preparedStatement.setString(6, collectInfo.getCollect2());
		preparedStatement.setString(7, collectInfo.getCollect3());
		preparedStatement.setString(8, collectInfo.getCollect4());
		preparedStatement.setString(9, collectInfo.getCollect5());
		if (preparedStatement.executeUpdate() > 0) {
			CloseResourceUtil.close(connection, preparedStatement, null);
			return "1";
		} else {
			CloseResourceUtil.close(connection, preparedStatement, null);
			return "0";
		}
	}

	public String changeCollectInfo(CollectInfo collectInfo, String oldID) throws SQLException {
		Connection connection = GetConnectionUtils.getoneConnection();
		String sql = "UPDATE collect_info SET food_name = ?, collect_total = ?, collect_1 = ?, collect_2 = ?, collect_3 = ?, collect_4 = ?, collect_5 = ? WHERE food_num = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
//		preparedStatement.setString(1, collectInfo.getFoodNum());
		preparedStatement.setString(1, collectInfo.getFoodName());
		preparedStatement.setInt(2, collectInfo.getCollectTotal());
		preparedStatement.setString(3, collectInfo.getCollect1());
		preparedStatement.setString(4, collectInfo.getCollect2());
		preparedStatement.setString(5, collectInfo.getCollect3());
		preparedStatement.setString(6, collectInfo.getCollect4());
		preparedStatement.setString(7, collectInfo.getCollect5());
		preparedStatement.setString(8, oldID);
		if (preparedStatement.executeUpdate() > 0) {
			CloseResourceUtil.close(connection, preparedStatement, null);
			return "1";
		} else {
			CloseResourceUtil.close(connection, preparedStatement, null);
			return "0";
		}
	}

	public String deleteCollectInfo(String foodNum) throws SQLException {
		Connection connection = GetConnectionUtils.getoneConnection();
		String sql = "DELETE FROM collect_info WHERE food_num = ?";
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, foodNum);
		if (preparedStatement.executeUpdate() > 0) {
			CloseResourceUtil.close(connection, preparedStatement, null);
			return "1";
		} else {
			CloseResourceUtil.close(connection, preparedStatement, null);
			return "0";
		}
	}

	public List<CollectInfo> collectInfoList(String keyword) throws SQLException {
		Connection connection = GetConnectionUtils.getoneConnection();
		String sql = "SELECT * FROM collect_info WHERE food_name LIKE ?";
		List<CollectInfo> list = new ArrayList<CollectInfo>();
		if (keyword == null) {

			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "%%");
			ResultSet result = preparedStatement.executeQuery();

			while (result.next()) {
				list.add(new CollectInfo(result.getString("food_num"), result.getString("food_name"),
						result.getInt("collect_total"), result.getString("collect_1"), result.getString("collect_2"),
						result.getString("collect_3"), result.getString("collect_4"), result.getString("collect_5")));
			}
			CloseResourceUtil.close(connection, preparedStatement, result);
			return list;
		} else {
			PreparedStatement preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, "%" + keyword + "%");
			ResultSet result = preparedStatement.executeQuery();

			while (result.next()) {
				list.add(new CollectInfo(result.getString("food_num"), result.getString("food_name"),
						result.getInt("collect_total"), result.getString("collect_1"), result.getString("collect_2"),
						result.getString("collect_3"), result.getString("collect_4"), result.getString("collect_5")));
			}
			CloseResourceUtil.close(connection, preparedStatement, result);
			return list;
		}
	}

}
